package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.leave_message;
import utils.DBconn;

public class LeaveDao {

	
			//ݿ
			// 删除原有的getConnection方法
			
//***************************************ѧҳʹ********************************************
			
			/*
			 * 1ѧѯϢ¼
			 * */
			public List<leave_message> getAloneLeaveMessages(String studentId) {
				List<leave_message> leaveMessages = new ArrayList<>();
				Connection conn = null;
				PreparedStatement stmt = null;
				ResultSet rs = null;
									    
				try { 	
					conn = DBconn.getConnection();
					//ȡidϢ
					String sql = "SELECT * FROM leave_message WHERE id = ?";
					stmt = conn.prepareStatement(sql);
					stmt.setString(1, studentId);
					rs = stmt.executeQuery();			        						        		        						     
									        
					while (rs.next()) {						        	
						leave_message lm = new leave_message();
									
						lm.setName(rs.getString("name")); 
						lm.setId(rs.getString("id")); 				            
						lm.setSdata(rs.getString("sdata"));
						lm.setEdata(rs.getString("edata"));
						lm.setStatus(rs.getString("status"));
						lm.setCourse(rs.getString("course"));	
						lm.setNumber(rs.getString("number"));
									            
						leaveMessages.add(lm);
					}
				}catch (SQLException e) {
						e.printStackTrace();
				}finally {
						try { if (rs != null) rs.close(); } catch (SQLException e) { /* ignored */ }
						try { if (stmt != null) stmt.close(); } catch (SQLException e) { /* ignored */ }
						try { if (conn != null) conn.close(); } catch (SQLException e) { /* ignored */ }	        
				}						    
						return leaveMessages;
			}		
			
			/*
			 * ��������/2��ѧ����ѯ���������Ϣ�ľ�������
			 * */
			public leave_message getOneLeaveMessages(String thisnumber) {
				leave_message lm = new leave_message();
				Connection conn = null;
				PreparedStatement stmt = null;
				ResultSet rs = null;
				
						    
				try {
					conn = DBconn.getConnection();
					//ȡ
					String sql = "SELECT * FROM leave_message WHERE number = ?";
					stmt = conn.prepareStatement(sql);
					stmt.setString(1, thisnumber);
					rs = stmt.executeQuery();						
					
					if (rs.next()) { // ����Ƿ��н��
						
											
						//д��ȥ
						lm.setNumber(rs.getString("number"));
						lm.setId(rs.getString("id"));                 					         
						lm.setName(rs.getString("name"));             
						lm.setClassroom(rs.getString("classroom"));   
						lm.setCourse(rs.getString("course"));         
						lm.setSdata(rs.getString("sdata"));           
						lm.setEdata(rs.getString("edata"));           
						lm.setSumdata(rs.getString("sumdata"));      
						lm.setPhone(rs.getString("phone"));           
						lm.setReason(rs.getString("reason"));         
						lm.setGo(rs.getString("go"));                 
						lm.setAddress(rs.getString("address"));       
						lm.setStatus(rs.getString("status"));         
						lm.setRefuse(rs.getString("refuse"));   
					} else {
					    System.out.println("û���ҵ�ƥ��ļ�¼��");
					}
					
						        					        						        						        
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					try { if (rs != null) rs.close(); } catch (SQLException e) { /* ignored */ }
					try { if (stmt != null) stmt.close(); } catch (SQLException e) { /* ignored */ }
					try { if (conn != null) conn.close(); } catch (SQLException e) { /* ignored */ }	        
				}						    
				  return lm;
			}								
			
			
			/*
			 * ��������/3���������嵥��Ϣ
			 * */
			public boolean addleave(String number, String name,String id,String classroom,String course,String sdata,String edata,String sumdata,String phone,String reason,String go,String address,String status,String refuse) {
				boolean b = false; // ��ʼ������ֵΪ false
			    Connection conn = null;
			    PreparedStatement stmt = null;
			    try {
			    	
			        // ��ȡ���ݿ�����
			        conn = DBconn.getConnection();
			        // д SQL 
			        String sql = "INSERT INTO leave_message (number, id, course, name, classroom, sdata, edata, sumdata, phone, reason, go, address, status, refuse) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			        stmt = conn.prepareStatement(sql);
			        
			        // ���ò���ֵ
			        stmt.setString(1, number);
			        stmt.setString(2, id); 
				    stmt.setString(3, course);
				    stmt.setString(4, name); 
				    stmt.setString(5, classroom); 
				    stmt.setString(6, sdata); 
				    stmt.setString(7, edata); 
				    stmt.setString(8, sumdata); 
				    stmt.setString(9, phone); 
				    stmt.setString(10, reason); 
				    stmt.setString(11, go); 
				    stmt.setString(12, address); 
				    stmt.setString(13, "未审核"); 
				    stmt.setString(14, refuse); 
			        
			        // ִ�в������
			        int rowsAffected = stmt.executeUpdate();
			        // �����Ӱ����������� 0�������÷���ֵΪ true
			        if (rowsAffected > 0) {
			            b = true;
			        }
			    } catch (SQLException e) {
			        e.printStackTrace();
			    } finally {
			        // �ر���Դ
			        try {
			            if (stmt != null) stmt.close();
			            if (conn != null) conn.close();
			        } catch (SQLException ex) {
			            ex.printStackTrace();
			        }
			    }
			    return b; // ���ز������
			     
			}
			
			/*
			 * ����������4��ѧ������һ���������
			 * */
			public boolean deleteLeaveMessage(String number) {
			    Connection conn = null;
			    PreparedStatement stmt = null;
			    int rowsAffected = 0;

			    System.out.print(number);
			    
			    try {
			        // ��ȡ���ݿ�����
			        conn = DBconn.getConnection();

			        // ׼SQLɾ
			        String sql = "DELETE FROM leave_message WHERE number = ?";
			        stmt = conn.prepareStatement(sql);
			        stmt.setString(1, number);

			        // ִ��ɾ������
			        rowsAffected = stmt.executeUpdate();

			        // �ύ����������ݿ����Ӳ������Զ��ύģʽ�£�
			        // conn.commit(); // ע�͵�����Ϊͨ�����Զ��ύģʽ�¹���

			    } catch (SQLException e) {
			        // ����SQL�쳣�������ӡ��ջ���ٻ��¼��־
			        e.printStackTrace();
			        // ���ܻ���Ҫ�ع�����������ݿ����Ӳ������Զ��ύģʽ�£�
			        // try { if (conn != null) conn.rollback(); } catch (SQLException ex) { ex.printStackTrace(); } // ע�͵�����Ϊͨ�����Զ��ύģʽ�¹���
			    } finally {
			        // �ر���Դ
			        try { if (stmt != null) stmt.close(); } catch (SQLException e) { /* ignored */ }
			        try { if (conn != null) conn.close(); } catch (SQLException e) { /* ignored */ }
			    }

			    // ����ɾ��������
			    if(rowsAffected>0) {
			    	return true;
			    }else {
			    	return false;
			    }
			    
			}
			
			
//***************************************����Աҳ��ʹ��********************************************		

			/*
			 * ����������1������Ա��ѯ������༶�������Ϣ
			 * */
			public List<leave_message> getClassLeaveMessages(List<String> idclass) {
			    List<leave_message> leaveMessages = new ArrayList<>();						    
			    ResultSet rs = null;
			    
			    //��ȡ
		        String sql = "SELECT leave_message.* " +buildSqlQuery(idclass);		
		        //�����У��鿴�����sql���
		        System.out.println("buildSqlQuery:"+sql);						    
			    
			    try (Connection conn = DBconn.getConnection();
			             PreparedStatement stmt = conn.prepareStatement(sql)) {					       
			      
			        	//ѭclassֵ
			        	int i;
			        	for(i=1;i<idclass.size();i++) {
			        		stmt.setString(i, idclass.get(i));
			        	}
			        	
			        rs = stmt.executeQuery();						        						        		        
			        while (rs.next()) {
			        	
			        	System.out.print("��ѯ������");
			            leave_message lm = new leave_message();
			            
			            lm.setName(rs.getString("name")); 
			            lm.setClassroom(rs.getString("classroom")); 
			            lm.setId(rs.getString("id")); 				            
			            lm.setSdata(rs.getString("sdata"));
			            lm.setEdata(rs.getString("edata"));
			            lm.setStatus(rs.getString("status"));
			            lm.setCourse(rs.getString("course"));	
			            lm.setNumber(rs.getString("number"));
			            
			            leaveMessages.add(lm);
			        }
			    } catch (SQLException e) {
			    	System.out.print("��ѯ������");
			        e.printStackTrace();
			    } finally {
			    	try { if (rs != null) rs.close(); } catch (SQLException e) { /* ignored */ }
			       	        
			    }
			    
			    return leaveMessages;
			}
			//��Ҫ�����ʦ�İ༶��̬ȥ��һ����ѯ���
		    public String buildSqlQuery(List<String> classArray) {
		        // ���� IN �Ӿ�Ĳ���
		        StringBuilder inClass = new StringBuilder();
		        for (int i =1; i < classArray.size(); i++) {
		        	inClass.append("?");
		            if (i < classArray.size() - 1) {
		            	inClass.append(", ");
		            }
		        }

		        // ������ SQL ��ѯ���
		        String sql = 
		                     "FROM student " +
		                     "JOIN leave_message ON student.id = leave_message.id " +
		                     "WHERE student.class IN (" + inClass.toString() + ") ";
		        
		        return sql;
		    }			
			
		    
			/*
			 * ��������/2������Ա��ˣ��޸����״̬�벵��ԭ��
			 * */
			public boolean updateleave(String number,String status,String refuse) {
				boolean b = false; // ��ʼ������ֵΪ false
			    Connection conn = null;
			    PreparedStatement stmt = null;
			    try {
			    			    	
			    	
			        // ��ȡ���ݿ�����
			        conn = DBconn.getConnection();
			        // д SQL 
			        String sql = "UPDATE leave_message SET status = ?, refuse = ? WHERE number = ?";
			        stmt = conn.prepareStatement(sql);
			        
			        // ���ò���ֵ
			        
				    stmt.setString(1, status); 
				    stmt.setString(2, refuse); 
				    stmt.setString(3, number);
			        
			        // ִ���޸Ĳ���
			        int rowsAffected = stmt.executeUpdate();
			        // �����Ӱ����������� 0�������÷���ֵΪ true
			        if (rowsAffected > 0) {
			            b = true;
			        }
			    } catch (SQLException e) {
			        e.printStackTrace();
			    } finally {
			        // �ر���Դ
			        try {
			            if (stmt != null) stmt.close();
			            if (conn != null) conn.close();
			        } catch (SQLException ex) {
			            ex.printStackTrace();
			        }
			    }
			    return b; // ���ز������
			     
			}

					
		
			
			
			
			
			
			
			
		
			
			
			
			
			
			
			
			
			
			
			
}
